Understanding One-to-One Relationships in MySQL

Course: MySQL Training

Topic: Understanding One-to-One Relationships

Course Link: MySQL Training Course

Demo Link: Register for a Free Course Demo

1. Introduction to One-to-One Relationships

A one-to-one relationship is a type of relationship between two database tables in which each record in one table is associated with no more than one record in another table. Similarly, each record in the second table is associated with no more than one record in the first table.

One-to-one relationships are useful when information about a single entity needs to be separated into multiple tables. This may be done to organize sensitive information, separate optional details, improve maintainability, or represent different aspects of the same entity.

For example, an application may store basic employee details in an employees table and additional employee information in an employee_profiles table. Each employee can have one profile, and each profile belongs to one employee.

Learning Objectives

• Understand the meaning and purpose of a one-to-one relationship.
• Identify real-world examples of one-to-one relationships.
• Learn how primary keys and foreign keys enforce relationships.
• Understand how a UNIQUE constraint helps enforce one-to-one cardinality.
• Create related tables using MySQL.
• Insert, retrieve, update, and delete related records.
• Compare one-to-one relationships with one-to-many and many-to-many relationships.
• Recognize common design errors and apply database integrity rules.

2. What Is a One-to-One Relationship?

A one-to-one relationship connects one row in a table with at most one corresponding row in another table. The relationship is commonly implemented using a foreign key that references a primary key or another unique key.

Consider two tables: employees and employee_profiles. The employees table stores general employee information, while the employee_profiles table stores additional information.

The employee_id in employee_profiles references the employee who owns that profile. A UNIQUE constraint on this foreign key prevents multiple profiles from referencing the same employee.

Key Characteristics

• Each row in the first table can be associated with zero or one row in the second table.
• Each row in the second table belongs to no more than one row in the first table.
• A foreign key helps ensure that a related parent record exists.
• A UNIQUE constraint on the foreign key prevents duplicate associations.
• A one-to-one relationship does not necessarily require every parent record to have a child record.

3. Real-World Examples of One-to-One Relationships

3.1 Person and Passport

A person may have one passport record in a simplified database design, and each passport record belongs to one person. Actual passport rules depend on the country and passport type, so the database model should reflect the application's real requirements.

3.2 Employee and Employee Profile

An employee's basic information can be stored separately from optional profile information such as an address, emergency contact, or biography.

3.3 User and User Settings

An application can maintain a user account in one table and a separate settings record for preferences such as notification options and display preferences.

3.4 Customer and Customer Details

A customer table may store essential identification details, while another table stores optional information such as delivery instructions or additional preferences.

3.5 Product and Product Specification

A product table can contain a product's name and price, while a separate table stores its technical specifications. This is appropriate when each product has one specification record in the application's data model.

4. Understanding Cardinality and Optionality

Cardinality describes how many records in one table can be associated with records in another table. Optionality describes whether the relationship is required or optional.

Important: a foreign key alone does not guarantee a one-to-one relationship. It generally allows multiple child records to reference the same parent. A UNIQUE constraint on the child foreign key is what prevents that duplication.

5. One-to-One Relationship vs. Other Relationships

Example of a One-to-Many Relationship

If one customer can place many orders, the orders table contains a foreign key referencing the customers table. The foreign key is not unique because several orders can belong to the same customer.

Example of a Many-to-Many Relationship

If students can enroll in many courses and each course can have many students, an intermediate table such as student_courses stores the associations between students and courses.

6. Primary Keys and Foreign Keys in One-to-One Relationships

6.1 Primary Key

A primary key uniquely identifies each row in a table. It must contain unique, non-NULL values. A table has one primary key, which may consist of one column or multiple columns.

CREATE TABLE users (
    user_id INT PRIMARY KEY,
    username VARCHAR(100) NOT NULL
);

6.2 Foreign Key

A foreign key ensures that a value in one table corresponds to an existing referenced key in another table, subject to the configured referential actions.

CREATE TABLE user_profiles (
    profile_id INT PRIMARY KEY,
    user_id INT,
    bio VARCHAR(500),
    FOREIGN KEY (user_id) REFERENCES users(user_id)
);

This foreign key validates the relationship, but it does not yet prevent a user from having multiple profiles. A UNIQUE constraint is needed for that.

6.3 UNIQUE Constraint

A UNIQUE constraint prevents duplicate non-NULL values in a column or combination of columns. In this example, it ensures that each user can be associated with at most one profile.

CREATE TABLE user_profiles (
    profile_id INT PRIMARY KEY,
    user_id INT NOT NULL UNIQUE,
    bio VARCHAR(500),
    CONSTRAINT fk_profile_user
        FOREIGN KEY (user_id)
        REFERENCES users(user_id)
);

Here, user_id INT NOT NULL UNIQUE means that every profile must reference a user, and no user can be referenced by more than one profile.

7. Designing a One-to-One Relationship with a Shared Primary Key

Another common design uses the same key as both the primary key of the child table and a foreign key referencing the parent table. This is called a shared-primary-key design.

CREATE TABLE users (
    user_id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(100) NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE
);

CREATE TABLE user_profiles (
    user_id INT PRIMARY KEY,
    bio VARCHAR(500),
    date_of_birth DATE,
    CONSTRAINT fk_user_profiles_user
        FOREIGN KEY (user_id)
        REFERENCES users(user_id)
        ON DELETE CASCADE
);

How This Design Works

• users.user_id uniquely identifies each user.
• user_profiles.user_id is both a primary key and a foreign key.
• The child table cannot contain two profiles for the same user because its primary key must be unique.
• The foreign key prevents a profile from referencing a nonexistent user.
• The child record is optional from the parent's perspective because a user can exist without a matching profile.
• ON DELETE CASCADE automatically deletes the associated profile when its user is deleted.

Advantages of a Shared Primary Key

• Enforces one-to-one cardinality naturally.
• Avoids an additional profile identifier when one is not needed.
• Makes the relationship straightforward to understand.
• Works well when the child record represents an extension of the parent entity.

Limitations

• The child table depends on the parent table's identifier.
• Creating a child record requires an existing parent record.
• It may be less convenient if the child entity needs an independent identifier for other relationships.

8. Choosing Between a UNIQUE Foreign Key and a Shared Primary Key

Choose the design that best matches the application's requirements. Neither design is universally better; both can enforce a one-to-one relationship correctly.

9. Creating a Complete One-to-One Relationship

The following example creates an employee database with a separate employee profile table. The profile table uses a separate primary key and a UNIQUE foreign key.

Step 1: Create the Database

CREATE DATABASE company_db;
USE company_db;

Step 2: Create the Employees Table

CREATE TABLE employees (
    employee_id INT AUTO_INCREMENT PRIMARY KEY,
    employee_name VARCHAR(100) NOT NULL,
    department VARCHAR(100) NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE,
    hire_date DATE
);

Step 3: Create the Employee Profiles Table

CREATE TABLE employee_profiles (
    profile_id INT AUTO_INCREMENT PRIMARY KEY,
    employee_id INT NOT NULL UNIQUE,
    address VARCHAR(255),
    emergency_contact VARCHAR(20),
    biography TEXT,
    CONSTRAINT fk_employee_profile
        FOREIGN KEY (employee_id)
        REFERENCES employees(employee_id)
        ON UPDATE CASCADE
        ON DELETE CASCADE
);

Step 4: Insert Employees

INSERT INTO employees
    (employee_name, department, email, hire_date)
VALUES
    ('Aarav Sharma', 'IT', '[email protected]', '2025-01-10'),
    ('Priya Mehta', 'HR', '[email protected]', '2025-02-15'),
    ('Rohan Verma', 'Finance', '[email protected]', '2025-03-20');

Step 5: Insert Employee Profiles

INSERT INTO employee_profiles
    (employee_id, address, emergency_contact, biography)
VALUES
    (1, 'Mumbai, Maharashtra', '9876501234', 'Software developer'),
    (2, 'Pune, Maharashtra', '9876505678', 'HR specialist');

Only two profile records have been inserted, even though three employees exist. This is valid because the schema permits an employee to exist without a profile.

Step 6: Verify the Records

SELECT * FROM employees;
SELECT * FROM employee_profiles;

10. Retrieving Related Data with JOIN

A JOIN combines rows from multiple tables using a related column. One-to-one relationships are commonly queried using INNER JOIN or LEFT JOIN.

10.1 INNER JOIN

INNER JOIN returns only employees who have a matching profile.

SELECT
    e.employee_id,
    e.employee_name,
    e.department,
    p.address,
    p.emergency_contact
FROM employees AS e
INNER JOIN employee_profiles AS p
    ON e.employee_id = p.employee_id;

10.2 LEFT JOIN

LEFT JOIN returns every employee, including employees who do not have a profile. For employees without profiles, columns from the profile table return NULL.

SELECT
    e.employee_id,
    e.employee_name,
    e.department,
    p.address,
    p.emergency_contact
FROM employees AS e
LEFT JOIN employee_profiles AS p
    ON e.employee_id = p.employee_id;

10.3 Find Employees Without Profiles

SELECT
    e.employee_id,
    e.employee_name
FROM employees AS e
LEFT JOIN employee_profiles AS p
    ON e.employee_id = p.employee_id
WHERE p.employee_id IS NULL;

10.4 Find Profiles with Employee Information

SELECT
    p.profile_id,
    p.employee_id,
    e.employee_name,
    p.address
FROM employee_profiles AS p
INNER JOIN employees AS e
    ON p.employee_id = e.employee_id;

11. Updating Records in a One-to-One Relationship

Related records can be updated independently. For example, changing an employee's address does not require changing the employee's name or department.

UPDATE employee_profiles
SET address = 'Thane, Maharashtra',
    emergency_contact = '9876512345'
WHERE employee_id = 1;

Update an employee's department separately:

UPDATE employees
SET department = 'Engineering'
WHERE employee_id = 1;

Use a suitable WHERE condition when updating records. Without a WHERE clause, an UPDATE statement can affect every row in the table.

12. Deleting Records and Referential Actions

Deletion behavior depends on the foreign key's referential action. The schema in this tutorial uses ON DELETE CASCADE, which deletes a profile automatically when its employee is deleted.

DELETE FROM employees
WHERE employee_id = 3;

If employee 3 has a profile, that profile is also deleted because of the cascading rule. If employee 3 has no profile, the employee record can still be deleted.

Common Referential Actions

Use cascading deletes carefully. If a profile contains records that must be retained for legal, auditing, or historical reasons, consider a restrictive relationship or a soft-delete approach instead.

13. Enforcing Data Integrity

Data integrity means maintaining accurate, consistent, and valid information in the database. In a one-to-one relationship, multiple constraints work together to preserve integrity.

Use these constraints to enforce rules at the database level rather than relying only on application code. Database constraints help protect data when multiple applications or users access the same database.

14. Handling Optional and Required Profiles

14.1 Optional Profile

In an optional-profile design, an employee can exist without a profile. The parent table does not require a corresponding child record. A child record, when present, must still reference an existing employee.

CREATE TABLE employee_profiles (
    employee_id INT PRIMARY KEY,
    address VARCHAR(255),
    FOREIGN KEY (employee_id)
        REFERENCES employees(employee_id)
);

14.2 Required Profile

If every employee must have a profile, the database needs a design that enforces that requirement. A foreign key from profiles to employees alone does not guarantee that every employee has a profile.

Common approaches include:

• Store the required profile attributes directly in the employees table.
• Use application logic and transactions to create both records together, with suitable controls against incomplete creation.
• Use a more advanced database design with appropriate enforcement mechanisms when a strict database-level guarantee is necessary.

For many applications, storing attributes in one table is simpler if the child record is mandatory and the separation offers no meaningful benefit.

15. One-to-One Relationships and Normalization

Normalization organizes database information to reduce unnecessary duplication and improve consistency. Splitting one entity across two tables may be useful, but a one-to-one relationship does not automatically make a design more normalized.

When Splitting a Table Can Help

• Some attributes are optional and frequently absent.
• Sensitive information requires separate access permissions or handling.
• A table contains groups of attributes used by different application features.
• Separating attributes makes the database structure easier to maintain.

When Splitting a Table May Not Help

• The tables are always queried together and have no meaningful separation.
• The split introduces unnecessary joins without improving clarity or security.
• The child table contains only one or two attributes that could sensibly remain in the parent.
• The design complicates transactions or application logic without a clear benefit.

Database design should reflect the application's business rules, query patterns, security requirements, and maintenance needs.

16. Using Transactions for Related Inserts

A transaction groups multiple database operations into one logical unit. Transactions are useful when an application creates a parent record and its corresponding profile and needs both operations to succeed together.

START TRANSACTION;

INSERT INTO employees
    (employee_name, department, email, hire_date)
VALUES
    ('Neha Kapoor', 'Marketing', '[email protected]', '2026-01-05');

SET @new_employee_id = LAST_INSERT_ID();

INSERT INTO employee_profiles
    (employee_id, address, emergency_contact, biography)
VALUES
    (@new_employee_id, 'Navi Mumbai, Maharashtra',
     '9876522222', 'Marketing specialist');

COMMIT;

If an error occurs during the transaction, the application can issue ROLLBACK rather than committing a partial result. This example assumes the tables use a transactional storage engine such as InnoDB and that error handling is performed appropriately by the client or stored program.

Transaction Commands

• START TRANSACTION begins a transaction.
• COMMIT saves the transaction's changes.
• ROLLBACK cancels uncommitted changes.

17. Checking Table Definitions and Constraints

After creating tables, verify their structure and foreign key rules.

17.1 Describe a Table

DESCRIBE employees;
DESCRIBE employee_profiles;

17.2 View the CREATE TABLE Statement

SHOW CREATE TABLE employee_profiles;

17.3 Inspect Existing Data

SELECT * FROM employees;
SELECT * FROM employee_profiles;

17.4 Find Duplicate Foreign Key Values

In a correctly constrained one-to-one table, this query should return no rows. It can help identify existing problems before adding a UNIQUE constraint to a legacy table.

SELECT
    employee_id,
    COUNT(*) AS profile_count
FROM employee_profiles
GROUP BY employee_id
HAVING COUNT(*) > 1;

18. Common Mistakes in One-to-One Relationships

19. Troubleshooting Common Errors

Error 1: Duplicate Entry for a UNIQUE Key

Cause: A profile is being inserted for an employee who already has a profile.

INSERT INTO employee_profiles
    (employee_id, address)
VALUES
    (1, 'Mumbai');

If employee 1 already has a profile, the UNIQUE constraint prevents another row from referencing that employee.

Solution: Check whether the profile already exists. Update it when appropriate instead of inserting a duplicate.

SELECT *
FROM employee_profiles
WHERE employee_id = 1;

Error 2: Foreign Key Constraint Fails

Cause: The referenced employee does not exist, or the referenced columns are incompatible with the foreign key definition.

Solution: Confirm the parent row exists and inspect the table definitions.

SELECT employee_id
FROM employees
WHERE employee_id = 100;

Error 3: Cannot Add a UNIQUE Constraint

Cause: Existing rows contain duplicate foreign key values.

Solution: Find and resolve duplicates before adding the constraint.

SELECT employee_id, COUNT(*) AS total
FROM employee_profiles
GROUP BY employee_id
HAVING COUNT(*) > 1;

Error 4: Cannot Delete a Parent Row

Cause: A child record references the parent, and the foreign key prevents deletion.

Solution: Review the configured delete action and decide whether to retain, remove, or reassign the child record. Do not remove related information without confirming the intended business rule.

20. Modifying an Existing Table to Enforce One-to-One Cardinality

If a child table already exists with a foreign key but no uniqueness rule, you can add a UNIQUE constraint after confirming that the existing data contains no duplicate values.

ALTER TABLE employee_profiles
ADD CONSTRAINT uq_employee_profile_employee
UNIQUE (employee_id);

If the foreign key column must always contain a value, ensure that existing rows satisfy this requirement before modifying the column.

ALTER TABLE employee_profiles
MODIFY employee_id INT NOT NULL;

Before changing a production table, review existing NULL values, duplicate values, dependencies, and the impact of schema changes on applications. The column type and signedness must also match the referenced key as required by MySQL.

21. Best Practices for Designing One-to-One Relationships

• Define the business rule before creating tables.
• Use a primary key to identify every record.
• Declare a foreign key to maintain referential integrity.
• Use a UNIQUE foreign key or shared primary key to enforce one-to-one cardinality.
• Use NOT NULL when the child record must always reference a parent.
• Decide explicitly whether the child record is optional.
• Select ON DELETE and ON UPDATE actions carefully.
• Keep each attribute in the table that best represents its meaning.
• Use transactions when related operations must succeed together.
• Validate constraints with test inserts, updates, and deletes.
• Use descriptive table names and constraint names.
• Avoid splitting tables unnecessarily when a single table is clearer and easier to maintain.
• Consider security and access requirements when storing sensitive information separately.

22. Practical Project: User Accounts and User Profiles

Project Objective: Build a simple database that stores user account details separately from optional profile information.

Requirements

• Each user must have a unique user ID and email address.
• A user may have zero or one profile.
• Every profile must reference an existing user.
• A user cannot have multiple profiles.
• Deleting a user should delete the related profile in this example.
• The database must support queries that show users with and without profiles.

Step 1: Create the Tables

CREATE DATABASE user_profile_project;
USE user_profile_project;

CREATE TABLE users (
    user_id INT AUTO_INCREMENT PRIMARY KEY,
    full_name VARCHAR(100) NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE profiles (
    profile_id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL UNIQUE,
    phone VARCHAR(20),
    city VARCHAR(100),
    about_me TEXT,
    CONSTRAINT fk_profiles_users
        FOREIGN KEY (user_id)
        REFERENCES users(user_id)
        ON DELETE CASCADE
);

Step 2: Add Sample Users

INSERT INTO users (full_name, email)
VALUES
    ('Amit Shah', '[email protected]'),
    ('Sara Khan', '[email protected]'),
    ('Dev Patel', '[email protected]');

Step 3: Add Profiles for Two Users

INSERT INTO profiles (user_id, phone, city, about_me)
VALUES
    (1, '9000011111', 'Mumbai', 'Database learner'),
    (2, '9000022222', 'Pune', 'Web developer');

Step 4: Display All Users and Their Profiles

SELECT
    u.user_id,
    u.full_name,
    u.email,
    p.phone,
    p.city,
    p.about_me
FROM users AS u
LEFT JOIN profiles AS p
    ON u.user_id = p.user_id
ORDER BY u.user_id;

Step 5: Attempt to Add a Duplicate Profile

INSERT INTO profiles (user_id, phone, city)
VALUES (1, '9000099999', 'Nashik');

This statement should fail if user 1 already has a profile, because profiles.user_id is UNIQUE.

Step 6: Find Users Without Profiles

SELECT
    u.user_id,
    u.full_name,
    u.email
FROM users AS u
LEFT JOIN profiles AS p
    ON u.user_id = p.user_id
WHERE p.user_id IS NULL;

Expected Learning Outcome

After completing the project, you should be able to create a one-to-one relationship, enforce uniqueness, preserve referential integrity, retrieve related data, and identify users who do not yet have profiles.

23. Practice Exercises

1. Create customers and customer_preferences tables with a one-to-one relationship.
2. Use a UNIQUE foreign key to ensure that each customer has no more than one preferences record.
3. Insert five customers and three preferences records.
4. Write a LEFT JOIN query that displays every customer and their preferences, including customers without preferences.
5. Write a query to identify customers without a preferences record.
6. Attempt to insert two preferences records for the same customer and observe the result.
7. Create a second example using a shared primary key rather than a separate child primary key.
8. Test the effect of ON DELETE CASCADE on related records.
9. Explain why a foreign key without a UNIQUE constraint does not enforce a one-to-one relationship.
10. Compare the advantages of keeping optional details in a separate table versus storing them in the main table.

24. Interview Questions and Answers

Q1. What is a one-to-one relationship in MySQL?

Answer: It is a relationship in which each record in one table is associated with at most one record in the other table, and vice versa.

Q2. How do you implement a one-to-one relationship in MySQL?

Answer: Create a foreign key in one table that references a key in the other table and enforce uniqueness on the foreign key. Alternatively, use the child table's primary key as its foreign key to the parent.

Q3. Is a foreign key alone enough to create a one-to-one relationship?

Answer: No. A foreign key alone generally permits multiple child rows to reference the same parent. A UNIQUE constraint or shared primary key is required to enforce one-to-one cardinality.

Q4. What is a shared primary key?

Answer: It is a design in which the child table's primary key is also a foreign key referencing the parent table's primary key.

Q5. Can a parent record exist without a child record?

Answer: Yes, unless the database design or additional enforcement mechanisms require the child record to exist. A foreign key from child to parent does not require every parent to have a child.

Q6. What is the purpose of a UNIQUE constraint?

Answer: It prevents duplicate non-NULL values in the constrained column or column combination. In a one-to-one relationship, it prevents multiple child records from referencing the same parent.

Q7. What is the difference between one-to-one and one-to-many relationships?

Answer: In a one-to-one relationship, each side is associated with at most one record on the other side. In a one-to-many relationship, one parent can be associated with multiple child records.

Q8. When should you use LEFT JOIN?

Answer: Use LEFT JOIN when you need all rows from the left table, including records without a matching row in the right table.

Q9. What does ON DELETE CASCADE do?

Answer: It automatically deletes matching child rows when the referenced parent row is deleted.

Q10. Why might profile information be stored in a separate table?

Answer: Separating profile data may help manage optional attributes, security, data organization, and maintenance. The decision should depend on the application's needs.

Q11. How can you find users without profiles?

Answer: Use LEFT JOIN and filter for NULL in a non-nullable profile key, such as WHERE p.user_id IS NULL.

Q12. What is referential integrity?

Answer: Referential integrity ensures that foreign key values reference valid parent keys, subject to the relationship's constraints and configured actions.

25. Quick Revision Checklist

• A one-to-one relationship associates each record with at most one record on the other side.
• A foreign key validates references to the parent table.
• A UNIQUE foreign key prevents multiple child records from referencing the same parent.
• A shared primary key is another way to enforce one-to-one cardinality.
• NOT NULL requires every child row to have a parent reference when applied to the foreign key.
• A parent can still exist without a child unless the design enforces required participation.
• INNER JOIN returns matching records; LEFT JOIN also returns unmatched rows from the left table.
• ON DELETE CASCADE removes dependent child rows when a parent is deleted.
• Transactions can help keep related inserts consistent.
• One-to-one relationships should be created when they reflect a real business requirement.

26. Conclusion

One-to-one relationships are an important part of relational database design in MySQL. They allow related information to be separated into distinct tables while maintaining a clear connection between records. A correctly designed relationship uses a foreign key to preserve referential integrity and a UNIQUE constraint or shared primary key to enforce one-to-one cardinality.

Understanding optional relationships, JOIN operations, constraints, referential actions, and transactions helps you build reliable databases that are easier to query, maintain, and expand. Practice by creating tables, inserting related records, testing duplicate references, and retrieving data with INNER JOIN and LEFT JOIN.

Learn more: Explore the MySQL Training Course

Register for a demo: Book a Course Demo